Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


I/O Configuration

The function of a parallel query is to split up query operations so that they more effectively take advantage of the system. One of the ways a parallel query does this is by allowing the processing of the query to continue while pieces of the query operation are stalled waiting for I/Os to complete. Parallel queries are not effective if the entire table is limited to one disk drive.

By striping the table across many drives, I/Os can be distributed and a higher level of parallelism can occur. Striping can be done with OS striping, with Oracle striping, or (better yet) with a hardware disk array. Refer to Chapters 14 and 15 for detailed information on this topic.

Large contiguous extents can also help performance in parallel query operations. During scan operations, the query coordinator splits contiguous ranges of blocks into large, medium, and small groups of blocks. Each query server is given a large group of blocks to start with, progressively working its way down to the small group of blocks until the scan is completed. This is done to try to balance the load done by each query server. If there are several large extents in a table, the query coordinator can find blocks to dispatch to the query servers much more easily.


TIP:  Remember to make your temporary tablespace up of several large extents on a striped volume. This arrangement helps sorting performance.

Degree of Parallelism

Properly distributing I/Os and the degree of parallelism are the two most important things to tune in the Parallel Query option. Tuning the degree of parallelism is partially trial and error and partially analysis. It is very important to take notes when you are experimenting with the degree of parallelism. Your first guess should be based on the following factors:

  The CPU capacity of your system. The number and capacity of CPUs have an effect on the number of query processes you should run.
  The capacity of the system to handle large numbers of processes. Some OSes can handle many simultaneous threads; others are more limited.
  The system load. If the system is already running at 100 percent capacity, the degree of parallelism doesn’t have much effect. If you are running at 90 percent, too many query processes can overload the system.
  The amount of query processing on the system. If most operations are updates but there are a few critical queries, you may want many query processes.
  The I/O capacity of the system. If your disks are striped or if you are using a disk array, you should be able to handle a large number of parallel queries.
  The types of operations. Are you doing a lot of full-table scans or sorts? These operations benefit greatly from parallel query servers.

All these parameters should have some influence on the degree of parallelism you set up for your system. Remember that the preceding points are just guidelines to help with your best guess as a starting point. Here are a few other suggestions:

  CPU-intensive operations such as sorts should indicate a lower degree of parallelism. CPU-bound tasks are already taking advantage of the CPUs and tend not to be waiting for I/O.
  Disk-intensive operations such as full-table scans should indicate a higher degree of parallelism. The more operations waiting on I/O, the more the system can benefit from another query server.
  Many concurrent processes should indicate a lower degree of parallelism. Too many processes can overload the system.

Once you determine your starting point, you can monitor your system by querying the dynamic performance table, V$PQ_SYSSTAT. You can do this with the query shown here:

SQL> select * from v$pq_sysstat;

STATISTIC                           VALUE
------------------------------ ----------
Servers Busy                            0
Servers Idle                           12
Servers Highwater                      16
Server Sessions                       380
Servers Started                         4
Servers Shutdown                        4
Servers Cleaned Up                      0
Queries Initiated                      21
DFO Trees                              77
Local Msgs Sent                   2459361
Distr Msgs Sent                         0
Local Msgs Recv'd                 2459318
Distr Msgs Recv'd                       0

13 rows selected.

When looking at the output from this query, you will find the following statistics quite useful:

  Servers Busy: This is the number of servers busy at any one time. Check this statistic several times to get a good idea of the average value. If the value is equal to the initialization parameter, PARALLEL_MIN_SERVERS, you have probably configured too many query servers.
  Servers Idle: This is the number of servers idle at any one time. If you always have many idle servers, consider reducing PARALLEL_MIN_SERVERS.
  Servers Started: The number of query servers that have started up in this instance. If the value for Servers Busy is low but you see a large number of Servers Started, you may be using query servers sporadically.
  Servers Shutdown: The number of query servers that have been shut down because they are idle. This value is most likely similar to the Servers Started value.

Once you determine your degree of parallelism, begin testing; evaluate the information you get from V$PQ_SYSSTAT and from your operating system-monitoring facilities. Keep an eye out for CPU usage and excessive waiting for I/O. If the CPU usage is too high, try reducing the degree of parallelism. If the CPU usage is too low and there is significant waiting for I/O, try increasing the degree of parallelism.

Remember that the degree of parallelism is determined by SQL hints, table definitions, and initialization parameters. The total number of query servers is determined by the initialization parameter, PARALLEL_MAX_SERVERS; the number started up initially is determined by the initialization parameter, PARALLEL_MIN_SERVERS.

The total number of query servers in use is the number of queries executed in parallel multiplied by their degree of parallelism. If you try to use more than PARALLEL_MAX_SERVERS, you will not be able to parallelize your query.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement.